/*
 * AreaDAO.java
 *
 * Created on 4 de diciembre de 2005, 21:32
 *
 */
package org.fiel.admin.dao;

import java.io.PrintStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.log4j.Logger;

import org.fiel.FielConstants;
import org.fiel.admin.struts.forms.AreaForm;

/**
 * @author Ing. Luis Pablo Gallo
 */
public class AreaDAO {
    private Logger logger;
    
    private Connection getConnection()
    throws Exception{
        Context ctx = new InitialContext();
        DataSource ds = (DataSource)ctx.lookup(FielConstants.DATA_SOURCE_NAME);
        return ds.getConnection();
    }
    
    public AreaDAO()
    throws Exception{
//        conn = null;
        logger = Logger.getLogger(AreaDAO.class);
        
//        try{
//            Context ctx = new InitialContext();
//            DataSource ds = (DataSource)ctx.lookup(FielConstants.DATA_SOURCE_NAME);
//            conn = ds.getConnection();
//        } catch(Exception ex) {
//            logger.error("Creating connection", ex);
//            throw ex;
//        }
    }
    
    public void create(AreaForm area)
    throws Exception {
        PreparedStatement pstmt = null;
        Connection conn = this.getConnection();
        
        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("INSERT INTO areas (are_nombre, are_orden) VALUES(?,?)");
            pstmt.setString(1, area.getNombre());
            pstmt.setString(2, area.getOrden());
            pstmt.execute();
            conn.commit();
            conn.setAutoCommit(true);
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception ex) {
            conn.rollback();
            logger.error("Inserting new cargo", ex);
            throw ex;
        } finally {
            try {
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) { }
        }
    }
    
    public List retrieveUsed()
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        List areas = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT DISTINCT are_id, are_nombre, are_orden FROM areas, recursos WHERE rec_are_id = are_id ORDER BY are_orden");
            AreaForm area;
            
            for(rset = pstmt.executeQuery(); rset.next(); areas.add(area)) {
                area = new AreaForm();
                area.setId(rset.getString("are_id"));
                area.setNombre(rset.getString("are_nombre"));
                area.setOrden(rset.getString("are_orden"));
            }
            
            rset.close();
            rset = null;
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception ex) {
            logger.error("Retrieving areas", ex);
            throw ex;
        } finally {
            try {
                if(rset != null)
                    rset.close();
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) { }
        }
        return areas;
    }

    public List retrieve()
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        List areas = new ArrayList();
        try {
            pstmt = conn.prepareStatement("SELECT * FROM areas ORDER BY are_nombre");
            AreaForm area;
            
            for(rset = pstmt.executeQuery(); rset.next(); areas.add(area)) {
                area = new AreaForm();
                area.setId(rset.getString("are_id"));
                area.setNombre(rset.getString("are_nombre"));
                area.setOrden(rset.getString("are_orden"));
            }
            
            rset.close();
            rset = null;
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception ex) {
            logger.error("Retrieving areas", ex);
            throw ex;
        } finally {
            try {
                if(rset != null)
                    rset.close();
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) { }
        }
        return areas;
    }
    
    public AreaForm retrieve(String id)
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        ResultSet rset = null;
        AreaForm area = null;
        try {
            pstmt = conn.prepareStatement("SELECT * FROM areas WHERE are_id = ?");
            pstmt.setString(1, id);
            rset = pstmt.executeQuery();
            if(rset.next()) {
                area = new AreaForm();
                area.setId(rset.getString("are_id"));
                area.setNombre(rset.getString("are_nombre"));
                area.setOrden(rset.getString("are_orden"));
            }
            
            rset.close();
            rset = null;
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception ex) {
            logger.error("Retrieving areas", ex);
            throw ex;
        } finally {
            try {
                if(rset != null)
                    rset.close();
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) { }
        }
        return area;
    }
    
    public void update(AreaForm area)
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("UPDATE areas SET are_nombre = ?, are_orden = ? WHERE are_id = ?");
            pstmt.setString(1, area.getNombre());
            pstmt.setString(2, area.getOrden());
            pstmt.setString(3, area.getId());
            pstmt.executeUpdate();
            conn.commit();
            conn.setAutoCommit(true);
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception ex) {
            conn.rollback();
            logger.error("Updating area", ex);
            throw ex;
        } finally {
            try {
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) { }
        }
    }
    
    public void delete(String id)
    throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        try {
            conn.setAutoCommit(false);
            pstmt = conn.prepareStatement("DELETE FROM areas WHERE are_id = ?");
            pstmt.setString(1, id);
            pstmt.executeUpdate();
            conn.commit();
            conn.setAutoCommit(true);
            
            pstmt.close();
            pstmt = null;
            
            conn.close();
            conn = null;
            
        } catch(Exception ex) {
            conn.rollback();
            logger.error("Deleting area", ex);
            throw ex;
        } finally {
            try {
                if(pstmt != null)
                    pstmt.close();
                if(conn != null)
                    conn.close();
            } catch(Exception ex) { }
        }
    }
    
}
